import time
import numpy
import pymysql
import pandas as pd
from datetime import datetime
from pymysql.cursors import Cursor, DictCursor

# 定义 CITY_DICT
CITY_DICT = {
    '4103': '洛阳', '4112': '三门峡', '4111': '漯河', '4110': '许昌', '4113': '南阳', '4115': '信阳', '4190': '济源', '4109': '濮阳', '4106': '鹤壁', '4101': '郑州', '4108': '焦作',
    '4102': '开封', '4105': '安阳', '4114': '商丘', '4104': '平顶山', '4116': '周口', '4107': '新乡', '4117': '驻马店',
    '4405': '汕头', '4406': '佛山', '4412': '肇庆', '4413': '惠州', '4403': '深圳', '4404': '珠海', '4408': '湛江', '4407': '江门', '4417': '阳江', '4453': '云浮', '4416': '河源',
    '4415': '汕尾', '4409': '茂名', '4452': '揭阳', '4451': '潮州', '4402': '韶关', '4418': '清远', '4419': '东莞', '4414': '梅州', '4420': '中山', '4401': '广州',
    '1502': '包头', '1503': '乌海', '1507': '呼伦贝尔', '1508': '巴彦淖尔', '1529': '阿拉善', '1505': '通辽', '1522': '兴安', '1525': '锡林郭勒', '1501': '呼和浩特', '1509': '乌兰察布',
    '1506': '鄂尔多斯', '1504': '赤峰',
    '2307': '伊春', '2327': '大兴安岭', '2309': '七台河', '2304': '鹤岗', '2301': '哈尔滨', '2308': '佳木斯', '2305': '双鸭山', '2311': '黑河', '2310': '牡丹江', '2302': '齐齐哈尔',
    '2303': '鸡西', '2312': '绥化', '2306': '大庆',
    '6532': '和田', '6543': '阿勒泰', '6523': '昌吉', '6542': '塔城', '6527': '博尔塔拉', '6528': '巴音郭楞', '6505': '哈密', '6540': '伊犁', '6529': '阿克苏', '6531': '喀什',
    '6530': '克孜勒苏柯尔克孜', '6502': '克拉玛依', '6504': '吐鲁番', '6501': '乌鲁木齐',
    '4290': '潜江', '4209': '孝感', '4211': '黄冈', '4203': '十堰', '4206': '襄阳', '4228': '恩施', '4208': '荆门', '4205': '宜昌', '4201': '武汉', '4210': '荆州', '4212': '咸宁',
    '4213': '随州', '4202': '黄石', '4207': '鄂州',
    '2104': '抚顺', '2101': '沈阳', '2102': '大连', '2106': '丹东', '2114': '葫芦岛', '2107': '锦州', '2112': '铁岭', '2108': '营口', '2109': '阜新', '2105': '本溪', '2110': '辽阳',
    '2103': '鞍山', '2111': '盘锦', '2113': '朝阳',
    '3703': '淄博', '3706': '烟台', '3702': '青岛', '3710': '威海', '3715': '聊城', '3716': '滨州', '3705': '东营', '3711': '日照', '3707': '潍坊', '3714': '德州', '3701': '济南',
    '3709': '泰安', '3713': '临沂', '3708': '济宁', '3704': '枣庄', '3717': '菏泽',
    '6102': '铜川', '6107': '汉中', '6110': '商洛', '6108': '榆林', '6106': '延安', '6101': '西安', '6103': '宝鸡', '6109': '安康', '6105': '渭南', '6104': '咸阳',
    '3101': '上海',
    '5203': '遵义', '5206': '铜仁', '5202': '六盘水', '5227': '黔南', '5204': '安顺', '5223': '黔西南', '5226': '黔东南', '5205': '毕节', '5201': '贵阳',
    '5002': '重庆', '5001': '重庆',
    '5404': '林芝', '5402': '日喀则', '5401': '拉萨', '5403': '昌都', '5406': '那曲', '5425': '阿里', '5405': '山南',
    '3406': '淮北', '3412': '阜阳', '3410': '黄山', '3405': '马鞍山', '3407': '铜陵', '3417': '池州', '3408': '安庆', '3411': '滁州', '3404': '淮南', '3403': '蚌埠', '3413': '宿州',
    '3416': '亳州', '3401': '合肥', '3418': '宣城', '3415': '六安', '3402': '芜湖',
    '3509': '宁德', '3501': '福州', '3503': '莆田', '3502': '厦门', '3505': '泉州', '3506': '漳州', '3507': '南平', '3504': '三明', '3508': '龙岩',
    '4301': '长沙', '4304': '衡阳', '4331': '湘西', '4306': '岳阳', '4307': '常德', '4308': '张家界', '4312': '怀化', '4303': '湘潭', '4305': '邵阳', '4302': '株洲', '4310': '郴州',
    '4311': '永州', '4313': '娄底', '4309': '益阳',
    '4604': '儋州', '4603': '三沙', '4602': '三亚', '4601': '海口',
    '3207': '连云港', '3206': '南通', '3213': '宿迁', '3212': '泰州', '3201': '南京', '3205': '苏州', '3210': '扬州', '3211': '镇江', '3209': '盐城', '3208': '淮安', '3203': '徐州',
    '3204': '常州', '3202': '无锡',
    '6302': '海东', '6325': '海南', '6326': '果洛', '6327': '玉树', '6323': '黄南', '6328': '海西', '6301': '西宁', '6322': '海北',
    '4507': '钦州', '4502': '柳州', '4510': '百色', '4505': '北海', '4504': '梧州', '4503': '桂林', '4511': '贺州', '4513': '来宾', '4512': '河池', '4509': '玉林', '4501': '南宁',
    '4508': '贵港', '4514': '崇左', '4506': '防城港',
    '6404': '固原', '6405': '中卫', '6402': '石嘴山', '6403': '吴忠', '6401': '银川',
    '3303': '温州', '3302': '宁波', '3309': '舟山', '3310': '台州', '3304': '嘉兴', '3311': '丽水', '3307': '金华', '3308': '衢州', '3306': '绍兴', '3301': '杭州', '3305': '湖州',
    '1302': '唐山', '1310': '廊坊', '1303': '秦皇岛', '1304': '邯郸', '1309': '沧州', '1311': '衡水', '1305': '邢台', '1307': '张家口', '1306': '保定', '1301': '石家庄', '1308': '承德',
    '8100': '香港（取最后一个值可按需修改）',
    '8200': '澳门（取最后一个值可按需修改）',
    '6204': '白银', '6203': '金昌', '6209': '酒泉', '6208': '平凉', '6201': '兰州', '6202': '嘉峪关', '6212': '陇南', '6207': '张掖', '6211': '定西', '6229': '临夏', '6205': '天水',
    '6210': '庆阳', '6206': '武威', '6230': '甘南',
    '5108': '广元', '5113': '南充', '5119': '巴中', '5106': '德阳', '5107': '绵阳', '5116': '广安', '5101': '成都', '5114': '眉山', '5120': '资阳', '5109': '遂宁', '5117': '达州',
    '5110': '内江', '5134': '凉山', '5105': '泸州', '5111': '乐山', '5103': '自贡', '5104': '攀枝花', '5115': '宜宾', '5133': '甘孜', '5132': '阿坝', '5118': '雅安',
    '1201': '天津',
    '3607': '赣州', '3602': '景德镇', '3603': '萍乡', '3605': '新余', '3608': '吉安', '3611': '上饶', '3601': '南昌', '3606': '鹰潭', '3610': '抚州', '3604': '九江', '3609': '宜春',
    '5306': '昭通', '5303': '曲靖', '5329': '大理', '5305': '保山', '5328': '西双版纳', '5333': '怒江', '5307': '丽江', '5325': '红河', '5304': '玉溪', '5326': '文山', '5334': '迪庆',
    '5308': '普洱', '5323': '楚雄', '5309': '临沧', '5301': '昆明', '5331': '德宏',
    '1403': '阳泉', '1401': '太原', '1411': '吕梁', '1407': '晋中', '1404': '长治', '1408': '运城', '1410': '临汾', '1409': '忻州', '1406': '朔州', '1402': '大同', '1405': '晋城',
    '1101': '北京',
    '2202': '吉林', '2208': '白城', '2201': '长春', '2207': '松原', '2204': '辽源', '2203': '四平', '2224': '延边', '2206': '白山', '2205': '通化'
}


class MysqlUtils(object):
    def __init__(self, *args):
        self.conn = pymysql.connect(
            host='127.0.0.1',
            user='root',
            password='sjk1234',
            db='scenic',
            port=3306,
            charset='utf8'
        )

    def get_scenic_data(self):
        """获取数据
        """
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = """
        SELECT LEFT(u.id_no,4) as citv_code, DATE_FORMAT(o.create_time, '%Y-%m') as month, COUNT(u.id) as visitor_count
        FROM ticket_order o join ticket_order_user_rel u on o.id = u.order_id
        WHERE
        LENGTH(u.id_no) =18 and o.pay_time is not null and o.pay_time != ''
        GROUP BY citv_code, DATE_FORMAT(o.create_time, '%Y-%m')
        """
        cursor.execute(sql)
        ret = cursor.fetchall()
        # 打印查询结果，查看实际的键名
        print(ret)
        new_list = []
        for item in ret:
            if item['citv_code'] not in CITY_DICT:
                continue
            new_list.append({
                'citv_code': item['citv_code'],
                'month': item['month'],
                'visitor_count': item['visitor_count'],
                'city_name': CITY_DICT[item['citv_code']]
            })
        df_city_monthly = pd.DataFrame(new_list)
        # 修正赋值操作
        df_city_monthly['month'] = pd.to_datetime(df_city_monthly['month'] + '01')
        print(df_city_monthly.head())

        def calculate_baseline(df, current_month, window_size=6):
            df_current = df[df['month'] == current_month]
            # 修正拼写错误
            history_start = current_month - pd.DateOffset(months=window_size)
            df_history = df[(df['month'] >= history_start) & (df['month'] < current_month)]
            # 修正列名和拼写错误
            df_baseline = df_history.groupby('city_name')['visitor_count'].agg(['mean', 'std']).reset_index()
            df_baseline.rename(columns={'mean': 'hist_mean', 'std': 'hist_std'}, inplace=True)
            df_merged = df_current.merge(df_baseline, on='city_name', how='left')
            return df_merged

        current_month = pd.to_datetime('2024-12-01')
        df_merged = calculate_baseline(df_city_monthly, current_month)
        df_merged['z_score'] = (df_merged['visitor_count'] - df_merged['hist_mean']) / df_merged['hist_std']
        df_increased = df_merged[df_merged['z_score'] > 3]
        df_reduce = df_merged[df_merged['z_score'] < -3]
        print(df_increased)
        print('-----------------')
        print(df_reduce)


if __name__ == '__main__':
    mu = MysqlUtils()
    mu.get_scenic_data()